﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace Zgq.CommonTools
{
    public partial class C5DataBaseHelper : IDisposable
    {
        public delegate string GetDbConnStringDelegate(string sysDatabaseName);

        public static GetDbConnStringDelegate GetDbConnString;
        private SqlConnection _sqlConnection;

        public SqlConnection SqlConnection
        {
            get { return _sqlConnection; }
        }

        private SqlTransaction _sqlTran;
        public SqlTransaction SqlTransaction
        {
            get { return _sqlTran; }
        }

        /// <summary>
        /// 使用缓存
        /// </summary>
        public static CacheFlag UseCache = CacheFlag.NoCache;

        private string sysDatabaseName = null;

        C5DataBaseHelper()
        {
            if (_sqlConnection != null)
                Dispose();
        }

        //static Dictionary<string, string> dic = new Dictionary<string, string>();

        //static string baseDic = System.AppDomain.CurrentDomain.BaseDirectory;

        //static string _cacheNameTemp = "{0}_SysBookDbConnStr";
        //public static string GetDbConnString(string sysDatabaseName)
        //{
        //    string _cacheName = string.Format(_cacheNameTemp, sysDatabaseName);

        //    return CacheHelper.Get<string>(_cacheName, (out DateTime expiresDt) =>
        //                        {
        //                            expiresDt = DateTime.Now.AddDays(100);

        //                            var connStr = XmlHelper.GetXmlAttributeVal(string.Format("{0}\\DB.xml", baseDic), string.Format("/InfolightDB/DataBase/{0}", "SYS_BOOKS"), "String");
        //                            if (connStr == null) throw new Exception("配置文件SYSBOOK，配置错误！");

        //                            string sqlStr = string.Format("select DataBaseConn from BOOKS_INFO where SysDataBase='{0}' and IsEnable=1 and DataBaseConn is not null and DataBaseConn <> ''",
        //                                                       sysDatabaseName);

        //                            var dbConn = SqlHelper.ExecuteScalar(connStr.ToString(), sqlStr, null);
        //                            if (connStr == null) throw new Exception("数据库连接配置错误");

        //                            return dbConn.ToString();
        //                        });


        //    //string connectionString = null;
        //    //if (dic.Keys.Contains(sysDatabaseName))
        //    //    connectionString = dic[sysDatabaseName];
        //    //else
        //    //{
        //    //    var connStr = XmlHelper.GetXmlAttributeVal(string.Format("{0}\\DB.xml", baseDic), string.Format("/InfolightDB/DataBase/{0}", sysDatabaseName), "String");
        //    //    if (connStr == null)
        //    //        throw new Exception("数据库连接配置错误");
        //    //    connectionString = string.Format("{0};Connection Lifetime=1800; Max Pool Size=200;Pooling=true;connect Timeout=180", connStr);
        //    //}
        //    ////EntityProvider.GetProviderConnectionString(sysDatabaseName));
        //    //return connectionString;
        //}



        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sysDatabase">数据库连接账套或者数据库连接字符串</param>
        /// <param name="isEnabletran">是否启用事务</param>
        /// <param name="isDatabaseName">是否是帐套名称</param>
        public C5DataBaseHelper(string sysDatabase, bool isEnabletran = true, bool isDatabaseName = true)
        {
            if (isDatabaseName)
            {
                _sqlConnection = new SqlConnection(GetDbConnString(sysDatabase));
                sysDatabaseName = sysDatabase;
            }
            else
            {
                _sqlConnection = new SqlConnection(sysDatabase);
            }

            _sqlConnection.Open();

            if (isEnabletran)
                _sqlTran = _sqlConnection.BeginTransaction();

            CreateSqlCommand();

        }

        private SqlCommand _sqlCom;
        private SqlCommand CreateSqlCommand()
        {
            _sqlCom = new SqlCommand();
            _sqlCom.Connection = _sqlConnection;
            _sqlCom.CommandTimeout = 1800;
            if (_sqlTran != null)
                _sqlCom.Transaction = _sqlTran;
            return _sqlCom;
        }
        private bool IsCommitedOrRollback = false;
        public void Commit()
        {
            _sqlTran.Commit();
            IsCommitedOrRollback = true;
        }

        public void Rollback()
        {
            _sqlTran.Rollback();
            IsCommitedOrRollback = true;
        }

        public void Dispose()
        {
            if (_sqlTran != null && !IsCommitedOrRollback)
                Rollback();
            if (_sqlConnection != null & _sqlConnection.State == System.Data.ConnectionState.Open)
                _sqlConnection.Close();

            if (_sqlCom != null)
            {
                _sqlCom.Dispose();
                _sqlCom = null;
            }
            if (_sqlConnection != null)
            {
                _sqlConnection.Dispose();
                _sqlConnection = null;
            }
        }

        /// <summary>
        /// 为执行命令准备参数
        /// </summary>
        /// <param name="cmd">SqlCommand 命令</param>
        /// <param name="conn">已经存在的数据库连接</param>
        /// <param name="trans">数据库事物处理</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="cmdText">Command text，T-SQL语句 例如 Select * from Products</param>
        /// <param name="cmdParms">返回带参数的命令</param>
        private void PrepareCommand(CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            //判断数据库连接状态
            if (_sqlConnection.State != ConnectionState.Open)
                _sqlConnection.Open();
            _sqlCom.Connection = _sqlConnection;
            _sqlCom.CommandText = cmdText;
            _sqlCom.CommandTimeout = 1800;
            //判断是否需要事物处理
            if (_sqlTran != null)
            {
                _sqlCom.Transaction = _sqlTran;
            }
            _sqlCom.CommandType = cmdType;

            //清空SqlCommand中的参数列表
            if (_sqlCom.Parameters != null)
                _sqlCom.Parameters.Clear();
            if (cmdParms != null)
            {
                foreach (var parm in cmdParms)
                    _sqlCom.Parameters.Add(parm);
            }
        }

        public DataTable GetDataTable(string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {
            DataSet ds = GetDataSet(cmdText, cmdType, cmdParms);
            DataTable table = null;
            if (ds != null && ds.Tables.Count > 0)
                table = ds.Tables[0];
            return table;

        }

        public DataSet GetDataSet(string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {
            PrepareCommand(cmdType, cmdText, cmdParms);

            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = _sqlCom;
            adapter.Fill(ds);
            return ds;

        }

        /// <summary>
        ///执行一个不需要返回值的SqlCommand命令，通过指定专用的连接字符串。
        /// 使用参数数组形式提供参数列表 
        /// </summary>
        /// <param name="cmdText">存储过程的名字或者 T-SQL 语句</param>
        /// <param name="cmdType">SqlCommand命令类型 (存储过程， T-SQL语句， 等等。)</param>
        /// <param name="commandParameters">以数组形式提供SqlCommand命令中用到的参数列表</param>
        /// <returns>返回一个数值表示此SqlCommand命令执行后影响的行数</returns>
        public int ExecteNonQuery(string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {
            PrepareCommand(cmdType, cmdText, cmdParms);

            int val = _sqlCom.ExecuteNonQuery();
            return val;
        }

        public object ExecuteScalar(string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {

            PrepareCommand(cmdType, cmdText, cmdParms);

            object val = _sqlCom.ExecuteScalar();

            return val;
        }

        /// <summary>
        /// 检查是否存在
        /// </summary>
        /// <param name="strSql">Sql语句</param>
        /// <param name="cmdParms">参数</param>
        /// <returns>bool结果</returns>
        public bool Exists(string strSql, params SqlParameter[] cmdParms)
        {
            int cmdresult = Convert.ToInt32(ExecuteScalar(strSql, CommandType.Text, cmdParms));
            if (cmdresult == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        #region 获取数据实体
        public List<T> GetModelList<T>(string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {
            PrepareCommand(cmdType, cmdText, cmdParms);

            SqlDataReader read = _sqlCom.ExecuteReader();


            List<T> query = ReaderToList<T>(read);
            read.Close();
            return query;
        }

        public T GetModel<T>(string cmdText, CommandType cmdType = CommandType.Text, params SqlParameter[] cmdParms)
        {
            PrepareCommand(cmdType, cmdText, cmdParms);

            SqlDataReader read = _sqlCom.ExecuteReader();

            T query = GetModel<T>(read);
            read.Close();
            return query;
        }



        private static List<T> ReaderToList<T>(IDataReader dr)
        {

            List<T> list = new List<T>();
            Type modelType = typeof(T);
            int count = dr.FieldCount;
            List<PropertyInfo> listNotNullPropertyInfo = GetPropertyInfos(count, dr, modelType);
            while (dr.Read())
            {
                T model = ReaderToModel<T>(dr, count, listNotNullPropertyInfo, modelType);

                list.Add(model);
            }
            return list;
        }

        private static List<PropertyInfo> GetPropertyInfos(int fieldCount, IDataReader dr, Type modelType)
        {
            List<PropertyInfo> list = null;
            List<string> drNameList = new List<string>();

            for (int i = 0; i <= fieldCount - 1; i++)
            {
                drNameList.Add(dr.GetName(i));
            }

            PropertyInfo[] property = modelType.GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
            if (property != null)
            {
                list = property.ToList().Where(c => !drNameList.Contains(c.Name)
                    && (c.PropertyType.Name == typeof(string).Name
                    || c.PropertyType.Name == typeof(String).Name
                    || c.PropertyType.Name == typeof(int).Name
                    || c.PropertyType.Name == typeof(Int32).Name
                    || c.PropertyType.Name == typeof(Int64).Name
                    || c.PropertyType.Name == typeof(decimal).Name)
                     ).ToList();
            }
            return list;
        }

        //这个类对可空类型进行判断转换，要不然会报错   
        private static object HackType(object value, Type conversionType)
        {
            if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null)
                    return null;
                System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            }

            object obj;

            try
            {
                obj = Convert.ChangeType(value, conversionType);
            }
            catch
            {
                obj = 0;//conversionType.ToString();
            }
            return obj;
        }

        private static bool IsNullOrDBNull(object obj)
        {
            return (obj == null || (obj is DBNull));
        }

        public static T GetModel<T>(IDataReader dr)   //根据dr 返回一条记录
        {
            if ((dr as SqlDataReader).HasRows == false) return default(T);
            Type modelType = typeof(T);      //Type 是一个类型的基类
            int count = dr.FieldCount;       //得到适配器存放的表中的列的个数
            List<PropertyInfo> listNotNullPropertyInfo = GetPropertyInfos(count, dr, modelType);

            dr.Read();

            return ReaderToModel<T>(dr, count, listNotNullPropertyInfo, modelType);

        }

        private static T ReaderToModel<T>(IDataReader dr, int fieldCount, List<PropertyInfo> listNotNullPropertyInfo, Type modelType)
        {
            T model = Activator.CreateInstance<T>();   //创建一个T类型的事例 T类必须有无参数的构造函数
            //foreach (PropertyInfo item in listNotNullPropertyInfo)
            //{
            //    if (item.PropertyType.Name == typeof(string).Name)
            //    {
            //        item.SetValue(model, Convert.ChangeType("", item.PropertyType), null);
            //    }
            //    if (item.PropertyType.Name == typeof(int).Name
            //  || item.PropertyType.Name == typeof(Int32).Name
            //  || item.PropertyType.Name == typeof(Int64).Name
            //  || item.PropertyType.Name == typeof(decimal).Name)
            //    {
            //        item.SetValue(model, Convert.ChangeType(0, item.PropertyType), null);
            //    }
            //}
            for (int i = 0; i < fieldCount; i++)            //逐列对一条记录进行遍历
            {
                if (!IsNullOrDBNull(dr[i]))            // 如果不为空
                {
                    PropertyInfo pi = modelType.GetProperty(dr.GetName(i), BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                    if (pi != null)
                    {
                        pi.SetValue(model, HackType(dr[i], pi.PropertyType), null);
                    }
                }
            }
            return model;
        }
        #endregion
    }
}
